---
title: "Untitled"
output: html_document
date: "2024-04-25"
---

############################################
Title: 4_06_Table_6.rmd
Purpose: This code runs the baseline aggregate level regressions in Table 6 
Last updated: 19-04-2024

Input:
     - CLO holdings data. Latest version: final_panel_clo.csv
     - Loan spreads. Latest version: "final_panel_loan.csv"
     
Output:
    - Table 7, Panel B
  
##########################################


0. Setup *Set your file path here*
```{r, include=FALSE, results='hide'}
rm(list=ls()) 

require(stargazer); require(lubridate); library(sandwich); library(ggplot2); require(dplyr); require(tidyverse); library(readxl); library(sandwich); library(lmtest); library(psych)
require(quantmod); require(dynlm); require(AER); require(vars); require(forecast); require(stargazer); require(strucchange); require(xts); require(lubridate); require(fGarch); require(zoo); require(timeSeries); library(sandwich); require(car);library(fBasics); library(ggplot2); require(dplyr); require(tidyverse); library(readxl); library(sandwich); library(QuantPsyc); library(tseries); library(latticeExtra); library(corrplot); library(psych) ; library(stringr)

#Assign dplyr verbs  
select <- dplyr::select
rename <- dplyr::rename
mutate <- dplyr::mutate
filter <- dplyr::filter
arrange <- dplyr::arrange
distinct <- dplyr::distinct
group_by <- dplyr::group_by
summarise <- dplyr::summarise
lag <- dplyr::lag
lead <- dplyr::lead

options(scipen=999)
options(digits=4)

#Define directories
global <- ".../2024_RFS_Replication_TOSUBMIT"

```




1. Create Exposure Measure
--------------------------------------------------

1.1 Identify non-OG loans in CLO data 
```{r, results="hide", include=FALSE}

#Load
holdings_df_merged <- read_csv(paste0(global, "/Data/final_panel_clo.csv", ""))


#Filter on 2013-2015 period and non-OG loans
holdings_sample <- holdings_df_merged %>% 
  filter(date_m >= "2013-01-01" & date_m <= "2017-12-31") %>% 
  filter(!LPC_MajorIndustry %in% c("Oil and Gas")) %>% 
  filter(!Issuer %in% c("Cash")) %>% 
  arrange(Deal, date_m, lin) %>% 
  select(Deal, date_m, Manager, Issuer, lin, Tranche, PositionDealCurrency, MoodyRating, SpoorRating, LPC_MajorIndustry)


#Collapse down to the LIN level
holdings_sample <- holdings_sample %>% 
  group_by(Deal, date_m, Issuer, lin) %>% 
  summarise(Manager = first(Manager),
            Tranche = first(Tranche),
            PositionDealCurrency = sum(PositionDealCurrency),
            MoodyRating = first(MoodyRating),
            SpoorRating = first(SpoorRating),
            LPC_MajorIndustry = first(LPC_MajorIndustry))


#List of unique LIN's in CLO data (These are the non-OG LIN's)
list_lin <- holdings_sample %>% 
  ungroup() %>% 
  distinct(lin)



```

1.2 Firm's Exposure to CLOs @ May 2014
```{r}

#total holdings of each firm in a CLO
firm_exp <- holdings_sample %>% 
  filter(date_m == "2014-05-01") %>% 
  group_by(Deal, Issuer, date_m) %>% 
  summarise(issuer_clo_holding = sum(PositionDealCurrency, na.rm = T))

#Total CLO holdings of a given firm
firm <- holdings_sample %>% 
  filter(date_m == "2014-05-01") %>% 
  group_by(Issuer, date_m) %>% 
  summarise(total_issuer_holdings = sum(PositionDealCurrency, na.rm = T))

firm <- left_join(firm, firm_exp, by=c("Issuer"="Issuer", "date_m"="date_m"))

#Calculate Firm Exposure to CLO
firm <- firm %>% 
  arrange(Deal, date_m) %>%
  select(Deal, date_m, Issuer, issuer_clo_holding, total_issuer_holdings) %>% 
  mutate(issuer_exp_to_clo = issuer_clo_holding/total_issuer_holdings) %>% 
  mutate(issuer_exp_to_clo = ifelse(issuer_exp_to_clo < 0, 0, issuer_exp_to_clo),
         issuer_exp_to_clo = ifelse(issuer_exp_to_clo > 1, 1, issuer_exp_to_clo)) 
```

1.3 CLO's exposure to Oil and Gas industry @ May 2014
```{r, results="hide", include=FALSE}

#CLO exp to OG
clo_og <- holdings_df_merged %>% 
  filter(date_m == "2014-05-01") %>% 
  filter(LPC_MajorIndustry == "Oil and Gas") %>% 
  group_by(Deal, date_m) %>% 
  summarise(total_og_holdings = sum(PositionDealCurrency, na.rm = T))

#Total CLO holdings
clo <- holdings_sample %>% 
  filter(date_m == "2014-05-01") %>% 
  group_by(Deal, date_m) %>% 
  summarise(total_clo_holdings = sum(PositionDealCurrency, na.rm = T))


clo <- left_join(clo, clo_og, by = c("Deal"="Deal", "date_m"="date_m"))

#Calculate clo exp to oil and gas
clo <- clo %>% 
  mutate(total_og_holdings = ifelse(is.na(total_og_holdings), 0, total_og_holdings)) %>% 
  mutate(clo_exp_to_og = total_og_holdings/total_clo_holdings) %>% 
  mutate(clo_exp_to_og = ifelse(clo_exp_to_og < 0, 0, clo_exp_to_og),
         clo_exp_to_og = ifelse(clo_exp_to_og > 1, 1, clo_exp_to_og)) 

```

1.4 Create Firm OG Exposure @ May 2014
```{r, results="hide", include=FALSE}

#Add 
firm <- left_join(firm, clo, by=c("Deal"="Deal"))

#Collapse to Issuer level
firm_lin <- firm %>% 
  mutate(issuer_og_exposure = (issuer_exp_to_clo * clo_exp_to_og)) %>% 
  group_by(Issuer) %>% 
  summarise(sum_issuer_og_exposure = sum(issuer_og_exposure, na.rm = T))


```


2. Setup Loan Spread data
----------------------------------------------------------------------

2.1 Setup LSTA Price data
```{r, results="hide", include=FALSE}

#load Loan spreads (from Ucloud script)
lsta <-   read_csv(paste0(global, "/Data/final_panel_loans.csv", ""), 
    col_types = cols(date_m = col_date(format = "%Y-%m-%d")))

#winsorize
lsta <- lsta %>% 
  mutate(s_wz=ifelse(loan_spread*100*100 > 3500,3500/(100*100),loan_spread)) %>% 
  mutate(loan_spread_w=ifelse(loan_spread*100*100 < 5,5/(100*100),s_wz)) %>% 
  select(-c(s_wz))

#Keep only loans in CLO data
lsta_sample <- semi_join(lsta, list_lin, by=c("LIN"="lin"))

#Keep
lsta_sample <- lsta_sample %>% 
  select(date_m, LIN, BorrowerName, price, loan_spread, loan_spread_w) 

#Add
holdings_sample <- left_join(holdings_sample, lsta_sample, by=c("lin"="LIN", "date_m"="date_m"))
 
#List of non-OG LIN's
issuer_lin_list <- holdings_sample %>% 
  ungroup() %>% 
  distinct(Issuer, lin, .keep_all = T) %>% 
  filter(!lin == "Unmapped") %>% 
  select(Issuer, lin, LPC_MajorIndustry)


```

2.2 Setup ELP data
```{r, results="hide", include=FALSE}

#Load
 elp <-  read_csv(paste0(global, "/Data/final_panel_loans.csv", ""), 
    col_types = cols(date_m = col_date(format = "%Y-%m-%d")))
 

#Select
elp <- elp %>% 
  select(date_m, LIN, residuals_elp_6, fitted_elp_6)


#Keep only loans in CLO data
elp_sample <- semi_join(elp, list_lin, by=c("LIN"="lin"))


#Add
lsta_sample <- left_join(lsta_sample, elp_sample, by=c("LIN"="LIN", "date_m"="date_m"))



```

2.3 Add Firm OG Exposure @ May 2014
```{r, results="hide", include=FALSE}

#Add 
issuer_lin_list <- left_join(issuer_lin_list, firm_lin, by=c("Issuer"="Issuer"))
lsta_sample <- left_join(lsta_sample, issuer_lin_list, by=c("LIN"="lin"))

```

2.4 Final formatting
```{r, results="hide", include=FALSE}

#Add Oil Shock DV
lsta_sample <- lsta_sample %>% 
  mutate(post = ifelse(date_m > "2014-09-01",1,0)) %>% 
  mutate(yr = year(date_m))


#Dummy for OG Exposure
lsta_sample <- lsta_sample %>% 
  ungroup() %>% 
  mutate(exp_quartile = ntile(sum_issuer_og_exposure, n = 4)) %>% 
  mutate(dv_top25_exp = ifelse(exp_quartile == 4, 1, 0)) %>% 
  mutate(dv_bottom25_exp = ifelse(exp_quartile == 1, 1, 0)) %>% 
  mutate(dv_top50_exp = ifelse(exp_quartile %in% c(3,4), 1, 0))


```


3. Panel Reg
-----------------------------------------------------

3.1 Table 7 Panel B
```{r, results="hide", include=FALSE}

data_panel_elp <-  lsta_sample %>% 
    filter(date_m > "2013-01-01" & date_m < "2018-01-01") %>% 
  filter(!Issuer == "Bluestem Brands Inc") %>% 
  select(date_m, Issuer, LIN, BorrowerName, price, loan_spread, loan_spread_w, residuals_elp_6, fitted_elp_6, sum_issuer_og_exposure,  exp_quartile, dv_top25_exp, dv_bottom25_exp, dv_top50_exp, post, yr) %>% 
  ungroup() %>% 
  na.omit()


mod3 <- feols(residuals_elp_6 ~ sum_issuer_og_exposure*post | Issuer + date_m, data = data_panel_elp, cluster = c( "LIN"))
mod5 <- feols(fitted_elp_6 ~ sum_issuer_og_exposure*post | Issuer + date_m, data = data_panel_elp, cluster = c( "LIN"))

etable(mod3, mod5, keep = c("sum_issuer_og_exposure x post", "dv_top25_exp x post"),  coefstat = "tstat")


```









